<?php

namespace App\Model;


use Illuminate\Support\Facades\DB;

class GiftStream extends BaseModel
{
    protected $table = 'gift_stream';

    protected $userTable = 'user';
    protected $roomTable = 'room';

    public $timestamps = false;
    protected $primaryKey = 'stream_id';

    public function statisticsGiftStream($input = []){
        $limit = $input['limit'] ?? 10;
        $page = $input['page'] ?? 0;
        $starttime = $input['start_date'] ?? 0;
        $endtime = $input['end_date'] ?? 0;
        $dm = $input['type'] ?? 'd';
        $having = '1=1';
        $format = '%Y-%m-%d';
        if($dm == 'd'){
            $format = '%Y-%m-%d';
        }else if($dm == 'm'){
            $format = '%Y-%m';
            if (!empty($starttime)){
                $starttime = substr($starttime, 0,7);
            }
            if(!empty($endtime)){
                $endtime = substr($endtime,0,7);
            }
        }

        if(!empty($starttime)){
            $having .= " AND '{$starttime}'<=FROM_UNIXTIME(change_timestamp,'{$format}')";
        }
        if(!empty($endtime)){
            $having .= " AND '{$endtime}'>=FROM_UNIXTIME(change_timestamp,'{$format}')";
        }
        $sql = 'SELECT FROM_UNIXTIME(change_timestamp,\''.$format.'\') as ctime,COUNT(*) as count, gift_id,gift_name,SUM(gift_num) as gift_sum,SUM(gift_num * gift_price) as price_sum FROM zb_gift_stream WHERE '.$having.' group BY gift_id,gift_name,FROM_UNIXTIME(change_timestamp,\''.$format.'\') ORDER BY ctime desc ,price_sum desc';

        $data = DB::connection('api')->select($sql);

        $totalStatistics = [];
        $totalStatistics['count'] = array_sum(array_column($data,'count'));
        $totalStatistics['gift_sum'] = array_sum(array_column($data,'gift_sum'));
        $totalStatistics['price_sum'] = array_sum(array_column($data,'price_sum'));
        $total = count($data);
        $list = collect($data)->forPage($page, $limit)->values()->toArray();
        return [ 'data' => $list, 'total' => $total, 'total_statistics' => $totalStatistics ];
    }


    public function roomGiftStream($input){
        $uuid = $input['uuid']??0;
        $from_uuid = $input['from_uuid']??0;
        $room_uid = $input['room_uid']??0;
        $start_time = isset($input['start_date']) && $input['start_date']?strtotime($input['start_date']):0;
        $end_time = isset($input['end_date']) && $input['end_date']?strtotime($input['end_date']):0;
        $limit = $input['limit']??10;


        $query = DB::connection($this->connection)->table($this->table);
        if ($uuid){
            $userData = DB::connection($this->connection)->table($this->userTable)->where('uuid',$uuid)->first(['user_id']);
            $query->where($this->table.'.user_id',$userData->user_id);
        }
        if ($from_uuid){
            $userData = DB::connection($this->connection)->table($this->userTable)->where('uuid',$from_uuid)->first(['user_id']);
            $query->where($this->table.'.from_user_id',$userData->user_id);
        }
        if ($room_uid){
            $roomData = DB::connection($this->connection)->table($this->roomTable)->where('room_uid',$room_uid)->first(['room_id']);
            $query->where($this->table.'.room_id',$roomData->room_id);
        }
        if ($start_time) $query->where('change_timestamp','>',$start_time);
        if ($end_time) $query->where('change_timestamp','<',$end_time);


        $data = $query
            ->leftJoin($this->userTable .' as user',$this->table.'.user_id','=','user.user_id')
            ->leftJoin($this->userTable .' as from_user',$this->table.'.from_user_id','=','from_user.user_id')
            ->leftJoin($this->roomTable .' as room',$this->table.'.room_id','=','room.room_id')
            ->where($this->table.'.room_id','!=' ,0)
            ->select('user.uuid','from_user.uuid as from_uuid','room.room_uid','gift_id','gift_name','gift_price','gift_image','gift_num','user.nickname as username','from_user.nickname as from_username','room.name','change_timestamp')
            ->orderBy('change_timestamp','desc')
            ->paginate($limit);
        foreach ($data->items() as $key =>$item){
//            var_dump($data->items()[$key]->gift_price );
            $data->items()[$key]->sum_price =  round($item->gift_price * $item->gift_num);
//            $data->items()[$key]->gift_price =  round($item->gift_price / 100,2);
            $data->items()[$key]->send_date =  $item->change_timestamp != 0 ?date('Y-m-d H:i:s',$item->change_timestamp): '';
        }
        return $data;
    }
}
